import pandas as pd
urlcsv = "http://archive.ics.uci.edu/static/public/890/data.csv"
dfs = pd.read_csv(urlcsv)
no_data = ["oprior", "z30", "zprior", "preanti", "str2", "strat", "offtrt", "cd40", "cd420", "cd80", "cd820"]
df = dfs.drop(no_data, axis=1)
df

df.head()

df.describe().round(2)

#Para revisar datos incompletos, sumar nulos.
df.isna().sum()

pidnum     0
cid        0
time       0
trt        0
age        0
wtkg       0
hemo       0
homo       0
drugs      0
karnof     0
race       0
gender     0
symptom    0
treat      0
dtype: int64

df[(df["time"]<=0 )| (df["wtkg"]<= 0)]

# Según el repositorio, hay 4 tratamientos posibles [0, 1, 2, 3] esto se confirma aquí.
df["trt"].value_counts().sort_index()

trt
0    532
1    522
2    524
3    561
Name: count, dtype: int64

df["homo"].value_counts()

homo
1    1414
0     725
Name: count, dtype: int64

df["gender"].value_counts()

gender
1    1771
0     368
Name: count, dtype: int64

df["cid"].value_counts()

cid
0    1618
1     521
Name: count, dtype: int64

df["hemo"].value_counts()

hemo
0    1959
1     180
Name: count, dtype: int64

df["race"].value_counts()

race
0    1522
1     617
Name: count, dtype: int64

df["symptom"].value_counts()

symptom
0    1769
1     370
Name: count, dtype: int64

df["treat"].value_counts()

treat
1    1607
0     532
Name: count, dtype: int64

df["drugs"].value_counts()

drugs
0    1858
1     281
Name: count, dtype: int64

# Los valores de la Escala Karnofsky se definen en el intervalo [0, 100].
# No hay ningún dato fuera de este rango, es consistente.
df[(df["karnof"] < 0) | (df["karnof"] > 100)]

# Se confirma el rango de edades visualmente

df[(df["age"] < 20) | (df['age'] > 60)]['age'].value_counts().sort_index()

age
12    3
13    3
14    6
15    3
16    7
17    4
18    7
19    7
61    2
62    5
63    6
64    2
65    3
66    1
67    2
68    2
69    1
70    2
Name: count, dtype: int64

df[["wtkg", "age", "karnof"]].describe().T[["mean","std","min","max"]]

print(df[df["wtkg"] > 120].shape[0])
print(df[ (df["wtkg"] < 40) & (df["age"]>15)].shape[0])

12
1

# Revisemos estos registros

df[df["wtkg"] > 120][["pidnum", "age", "wtkg"]]

# Se ve razonable.

df[(df["wtkg"] < 40) & (df["age"]>15)]

# Esta persona tiene 30 años y pesa 36.78 kg con un karnof de 90. Se ve sospechoso.

df[(df["wtkg"] < 40)]

# La variable treat se puede obtener de trt, entonces, cuando trt sea 0 treat debe ser 0. 
# Vemos que no hay instancias donde esto no se cumpla. La variable treat es lo que dice ser.

print(df[(df["trt"] == 0) & (df["treat"] == 0)].shape[0])
print(df[df["treat"] == 0].shape[0])

532
532

import matplotlib.pyplot as plt
print(df[['age', 'karnof']].corr(method='spearman'))
plt.scatter(df['age'], df['karnof'])
plt.xlabel('Edad')
plt.ylabel('Índice Karnofsky')
plt.show()

             age    karnof
age     1.000000 -0.084544
karnof -0.084544  1.000000

print(df[['wtkg', 'karnof']].corr(method='spearman'))
plt.scatter(df['wtkg'], df['karnof'])
plt.xlabel('Peso')
plt.ylabel('Índice Karnofsky')
plt.show()

            wtkg    karnof
wtkg    1.000000  0.040878
karnof  0.040878  1.000000

import seaborn as sns
import matplotlib.pyplot as plt

vars_numericas = ["time", "age", "wtkg", "karnof"]
corr = df[vars_numericas].corr(method="pearson")

plt.figure(figsize=(5, 5))
sns.heatmap(corr, annot=True, fmt=".2f", cmap="BuPu")
plt.title("Matriz de correlación entre variables continuas")
plt.show()

import matplotlib.pyplot as plt
urlcsv = "http://archive.ics.uci.edu/static/public/890/data.csv"
df = pd.read_csv(urlcsv)
vars_interes = ['age', 'wtkg', 'karnof']

# Un boxplot separado para cada variable
for var in vars_interes:
    plt.figure(figsize=(6,4))
    df.boxplot(column=var)
    plt.title(f'Boxplot de {var}')
    plt.ylabel(var)
    plt.grid(True)
    plt.show()

import pandas as pd
import numpy as np
from scipy.stats import chi2
import matplotlib.pyplot as plt

# Cargar los datos nuevamente
file_path = "http://archive.ics.uci.edu/static/public/890/data.csv"
df = pd.read_csv(file_path)

# Seleccionar variables relevantes
data = df[['age', 'wtkg']]

# Calcular media y matriz de covarianza
mean_vec = np.mean(data, axis=0)
cov_matrix = np.cov(data, rowvar=False)
inv_cov_matrix = np.linalg.inv(cov_matrix)

# Función para distancia de Mahalanobis
def mahalanobis_distance(x, mean_vec, inv_cov_matrix):
    x_minus_mu = x - mean_vec
    return np.sqrt(x_minus_mu @ inv_cov_matrix @ x_minus_mu.T)

# Calcular distancias
distances = data.apply(lambda row: mahalanobis_distance(row, mean_vec, inv_cov_matrix), axis=1)

# Umbral para p < 0.001
threshold = np.sqrt(chi2.ppf(0.999, df=data.shape[1]))
outliers = distances > threshold
outlier_points = data[outliers]

# Gráfico
plt.figure(figsize=(8, 6))
plt.scatter(data['age'], data['wtkg'], c='blue', label='Datos normales')
plt.scatter(outlier_points['age'], outlier_points['wtkg'], c='red', label='Outliers')
plt.xlabel('Edad')
plt.ylabel('Peso (kg)')
plt.title('Outliers (Distancia de Mahalanobis)')
plt.legend()
plt.grid(True)
plt.show()

outlier_points.head()

import pandas as pd
import numpy as np
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import DBSCAN
import matplotlib.pyplot as plt

# Cargar los datos nuevamente
file_path = "http://archive.ics.uci.edu/static/public/890/data.csv"
df = pd.read_csv(file_path)

data = df[['age', 'wtkg']]

scaler = StandardScaler()
data_scaled = scaler.fit_transform(data)
dbscan = DBSCAN(eps = 0.6, min_samples = 10)
labels = dbscan.fit_predict(data_scaled)

data_outliers = data.copy()
data_outliers['cluster'] = labels

outliers_detectados = data_outliers[data_outliers['cluster'] == -1]
print(f"Número de outliers detectados: {outliers_detectados.shape[0]}")
labels = data_outliers['cluster'].values
unique_labels = np.unique(labels)

plt.figure(figsize=(8,6))

for lab in unique_labels:
    idx = labels == lab
    if lab == -1:
        # Outliers (DBSCAN noise)
        plt.scatter(data_outliers.loc[idx, 'age'],
                    data_outliers.loc[idx, 'wtkg'],
                    c='black', marker='x', label='Outliers')
    else:
        plt.scatter(data_outliers.loc[idx, 'age'],
                    data_outliers.loc[idx, 'wtkg'],
                    label=f'Cluster {lab}')

plt.title("Clusters y outliers detectados por DBSCAN (Age vs Weight)")
plt.xlabel("Edad (age)")
plt.ylabel("Peso (wtkg)")
plt.legend()
plt.show()

Número de outliers detectados: 21

df[(df["wtkg"] > 200 ) | (df["wtkg"] < 20)]

df[(df["age"] > 100) | (df["age"]<0)]

df.duplicated().sum()

np.int64(0)

# Cada paciente tiene un id único.
df['pidnum'].is_unique

True

df2 = pd.read_csv("CountyHealthData_2014-2015.csv")

#a)
dfg = df2.groupby('State')['Adult smoking'].mean().sort_values(ascending=False)
print(dfg.head(5))


#b)
import sqlite3  
import sqlalchemy as sqla

db = sqla.create_engine('sqlite:///datos.sqlite')
df2.to_sql('datos', con=db, if_exists='append', index=False)

tabla = pd.read_sql("SELECT * FROM datos", db)
query = """
SELECT State, AVG("Adult smoking") AS "Smoking rate" FROM datos GROUP BY State ORDER BY "Smoking rate" DESC LIMIT 5;"""

resultado = pd.read_sql(query, db)
resultado

State
KY    0.285237
AK    0.272043
WV    0.267182
TN    0.259626
MO    0.254155
Name: Adult smoking, dtype: float64

import pandas as pd
import numpy as np
import matplotlib

df2 = pd.read_csv("CountyHealthData_2014-2015.csv")
df2

dfFEI9 = df2[df2["Food environment index"]>=9]
dfFEI9u = dfFEI9.drop_duplicates(['State','County'])

dfFEI9["State"].value_counts().head(5)

State
VA    64
ND    33
MN    31
NY    19
MD    18
Name: count, dtype: int64

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('sqlite:///countyhealth.db', echo=False)
county_health = df2.to_sql('county_health', con = engine, index = False, if_exists = 'replace')

query = """ 
SELECT State, County, [Food environment index]
FROM county_health
WHERE [Food environment index] >= 9
"""
df2_filtrado = pd.read_sql(query, con = engine)

top5_estados = (df2_filtrado.groupby('State')
               .size()
                .sort_values(ascending = False)
                .head(5)
               
               )

print(df2_filtrado.head())
print(top5_estados)

  State             County  Food environment index
0    CO     Douglas County                   9.111
1    CT  Litchfield County                   9.020
2    GA     Forsyth County                   9.119
3    GA     Forsyth County                   9.000
4    GA      Harris County                   9.117
State
VA    64
ND    33
MN    31
NY    19
MD    18
dtype: int64

nuevo = df[["State","County", "Dentists", "Primary care physicians", "2011 population estimate"]]

elegidos = nuevo[nuevo["Dentists"] > 5*nuevo["Primary care physicians"]]
sind = elegidos.groupby(["State", "County"])[["Dentists", "Primary care physicians", "2011 population estimate"]].agg({
    "Dentists": "mean",
    "Primary care physicians": "mean",
    "2011 population estimate": "mean"}).reset_index()

sind["per capita"] = nuevo["Dentists"] / nuevo["2011 population estimate"]

nuevo = sind.sort_values(by="Dentists", ascending = False)

percapit = sind.sort_values(by = "per capita", ascending = False).head(3)

print("Counties en que hay mas de 5 veces la cantidad de dentistas, de las que hay de primary care physicians, ordenado por mayor cantidad de dentistas")
nuevo[["County", "Dentists", "Primary care physicians"]]


print("Counties con mayor dentistas per capita")
percapit[["County", "Dentists", "Primary care physicians", "per capita"]]

Counties en que hay mas de 5 veces la cantidad de dentistas, de las que hay de primary care physicians, ordenado por mayor cantidad de dentistas
Counties con mayor dentistas per capita

import plotly.express as px
df2_u = df2.drop_duplicates("FIPS")
condados_por_estado = df2_u.groupby('State')['FIPS'].count().reset_index(name = 'num_condados')
estados_mas_de_100 = condados_por_estado[condados_por_estado['num_condados'] >= 100]
estados_mas_de_100

fig = px.treemap(estados_mas_de_100,
                 path=['State'],
                 values='num_condados',
                 title='Estados con más de 100 condados únicos')
fig.show()

!pip install squarify

Requirement already satisfied: squarify in c:\users\jtjua\anaconda3\lib\site-packages (0.4.4)

import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select, func
import matplotlib.pyplot as plt
import squarify

# 1. Leer CSV
df = pd.read_csv("CountyHealthData_2014-2015.csv")

# 2. Guardarlo en SQLite en una tabla nueva
engine = create_engine("sqlite:///healthdata.db")
df.to_sql("county_health_new", engine, if_exists="replace", index=False)

# 3. Reflejar la tabla
metadata = MetaData()
metadata.reflect(bind=engine)

county_health = Table("county_health_new", metadata, autoload_with=engine)

# 4. Query: contar condados únicos por estado y filtrar los >100
stmt = (
    select(
        county_health.c.State,
        func.count(func.distinct(county_health.c.FIPS)).label("num_condados")
    )
    .group_by(county_health.c.State)
    .having(func.count(func.distinct(county_health.c.FIPS)) >= 100)
    .order_by(func.count(func.distinct(county_health.c.FIPS)).desc())
)

with engine.connect() as conn:
    result = conn.execute(stmt).fetchall()

for row in result:
    print(row)


# Convertir el resultado en DataFrame
result_df = pd.DataFrame(result, columns=['State', 'num_condados'])

# Graficar treemap
plt.figure(figsize=(12,8))
squarify.plot(sizes=result_df['num_condados'], 
              label=result_df['State'], 
              alpha=0.8)

plt.title('Número de condados (>= 100) por estado', fontsize=16)
plt.axis('off')
plt.show()

('TX', 237)
('GA', 159)
('VA', 133)
('KY', 120)
('MO', 115)
('IL', 102)
('KS', 101)
('NC', 100)

import pandas as pd

# Con lista de listas
tipo = ['Grupos paramilitares', 'Grupos armados no identificados', 'Guerrillas', 'Fuerza pública']
valor = [8903,6406, 3899, 2399 ]

df_lista = pd.DataFrame({ "tipo": tipo, "valor" : valor} )
print("DataFrame desde lista de listas:")
df_lista

DataFrame desde lista de listas:

import matplotlib.pyplot as plt
figura = plt.bar(df_lista["tipo"], df_lista["valor"], color = '0.55', width = 0.5 )

for bar in figura:
    altura = bar.get_height()
    plt.text( bar.get_x() + bar.get_width() / 2, altura + 15,                    
        f'{int(altura):,}',             
        ha='center', va='bottom', fontsize=9) #Valor en Y 

ax = plt.gca()        # Obtener los ejes actuales

for spine in ['top', 'right', 'left']:
    ax.spines[spine].set_visible(False)

ax.tick_params(axis='y', left=False, labelleft=True)

plt.ylabel("Número de victimas", fontsize=10) #Titulo y 
plt.xticks(fontsize = 6) #Nombres x
plt.title("Asesinatos selectivos", fontweight = "bold", fontsize = 12)
plt.show()

	pidnum	cid	time	trt	age	wtkg	hemo	homo	drugs	karnof	race	gender	symptom	treat
0	10056	0	948	2	48	89.8128	0	0	0	100	0	0	0	1
1	10059	1	1002	3	61	49.4424	0	0	0	90	0	0	0	1
2	10089	0	961	3	45	88.4520	0	1	1	90	0	1	0	1
3	10093	0	1166	3	47	85.2768	0	1	0	100	0	1	0	1
4	10124	0	1090	0	43	66.6792	0	1	0	100	0	1	0	0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
2134	990021	0	1091	3	21	53.2980	1	0	0	100	0	1	0	1
2135	990026	0	395	0	17	102.9672	1	0	0	100	1	1	0	0
2136	990030	0	1104	2	53	69.8544	1	1	0	90	1	1	0	1
2137	990071	1	465	0	14	60.0000	1	0	0	100	0	1	0	0
2138	990077	0	1045	3	45	77.3000	1	0	0	100	0	1	0	1

	pidnum	cid	time	trt	age	wtkg	hemo	homo	drugs	karnof	race	gender	symptom	treat
count	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00	2139.00
mean	248778.25	0.24	879.10	1.52	35.25	75.13	0.08	0.66	0.13	95.45	0.29	0.83	0.17	0.75
std	234237.29	0.43	292.27	1.13	8.71	13.26	0.28	0.47	0.34	5.90	0.45	0.38	0.38	0.43
min	10056.00	0.00	14.00	0.00	12.00	31.00	0.00	0.00	0.00	70.00	0.00	0.00	0.00	0.00
25%	81446.50	0.00	727.00	1.00	29.00	66.68	0.00	0.00	0.00	90.00	0.00	1.00	0.00	1.00
50%	190566.00	0.00	997.00	2.00	34.00	74.39	0.00	1.00	0.00	100.00	0.00	1.00	0.00	1.00
75%	280277.00	0.00	1091.00	3.00	40.00	82.56	0.00	1.00	0.00	100.00	1.00	1.00	0.00	1.00
max	990077.00	1.00	1231.00	3.00	70.00	159.94	1.00	1.00	1.00	100.00	1.00	1.00	1.00	1.00

	mean	std	min	max
wtkg	75.125311	13.263164	31.0	159.93936
age	35.248247	8.709026	12.0	70.00000
karnof	95.446470	5.900985	70.0	100.00000

	pidnum	age	wtkg
304	50629	46	123.37920
629	110717	36	120.65760
945	170972	38	130.63680
1060	190408	39	127.70000
1084	210039	30	149.00000
1177	220432	30	129.00000
1441	251059	45	127.00800
1569	270883	28	125.64720
1622	300517	31	122.47200
1707	320357	28	159.93936
1773	330242	30	135.17280
1837	520014	33	120.65760

	age	wtkg
1	61	49.4424
49	67	71.0000
104	70	73.9368
271	68	90.5000
518	68	70.7616

Parte 1 Taller Modelación y Simulación 4¶

Integrantes:¶

Entendimiento del negocio (CRISP-DM)¶

Evaluar el panorama¶

Determinar los objetivos del negocio¶

Preguntas de negocio.¶

Éxito del negocio¶

Riesgos del negocio¶

Limitaciones del negocio¶

Determinar las metas de la minería de datos¶

Producir un plan del proyecto¶

Carga de datos¶

Diccionario de datos¶

Un primer análisis¶

Vistazo a los datos (head)¶

Estadísticos descriptivos (describe)¶

Análisis de lo visto hasta el momento¶

Análisis de calidad de los datos¶

¿ Qué dimensiones de calidad de datos se van a analizar?¶

- Completitud:¶

- Conformidad/Validez:¶

- Consistencia:¶

Detección de outliers unidimensionales para edad, peso y coeficiente de Karnofsky¶

Detección de outliers para edad y peso¶

- Precisión/Exactitud:¶

- Duplicidad/Unicidad:¶

- Integridad:¶

Pasos futuros a desarrollar¶

Punto 2¶

Condados con FEI mayores a 9 (Usando filtrado de Pandas)¶

Condados con FEI mayores a 9 (Usando SQLite)¶

1.3¶

¿Qué estados tienen más de 100 condados? (Pandas)¶

¿Qué estados tienen más de 100 condados? (SQLite)¶

Punto 4.¶

	pidnum	cid	time	trt	age	wtkg	hemo	homo	karnof	gender	symptom	treat
1931	610043	0	225	2	30	36.78696	0	1	90	1	0	1
2014	910034	0	1121	2	13	32.65920	1	0	100	1	1	1
2072	950056	1	324	1	12	31.00000	1	0	100	1	0	1

	State	Region	Division	County	FIPS	GEOID	SMS Region	Year	Premature death	Poor or fair health	...	Drug poisoning deaths	Uninsured adults	Uninsured children	Health care costs	Could not see doctor due to cost	Other primary care providers	Median household income	Children eligible for free lunch	Homicide rate	Inadequate social support
0	AK	West	Pacific	Aleutians West Census Area	2016	2016	Insuff Data	1/1/2014	NaN	0.122	...	NaN	0.374	0.250	3791.0	0.185	216.0	69192	0.127	NaN	0.287
1	AK	West	Pacific	Aleutians West Census Area	2016	2016	Insuff Data	1/1/2015	NaN	0.122	...	NaN	0.314	0.176	4837.0	0.185	254.0	74088	0.133	NaN	NaN
2	AK	West	Pacific	Anchorage Borough	2020	2020	Region 22	1/1/2014	6827.0	0.125	...	15.37	0.218	0.096	6588.0	0.119	135.0	71094	0.319	6.29	0.160
3	AK	West	Pacific	Anchorage Borough	2020	2020	Region 22	1/1/2015	6856.0	0.125	...	17.08	0.227	0.123	6582.0	0.119	148.0	76362	0.334	5.60	NaN
4	AK	West	Pacific	Bethel Census Area	2050	2050	Insuff Data	1/1/2014	13345.0	0.211	...	NaN	0.394	0.124	5860.0	0.200	169.0	41722	0.668	12.77	0.477
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
6104	WY	West	Mountain	Uinta County	56041	56041	Insuff Data	1/1/2015	7436.0	0.135	...	18.66	0.192	0.090	7600.0	0.123	47.0	60953	0.273	NaN	NaN
6105	WY	West	Mountain	Washakie County	56043	56043	Insuff Data	1/1/2014	6580.0	0.106	...	NaN	0.225	0.086	8202.0	0.099	47.0	49533	0.328	NaN	0.133
6106	WY	West	Mountain	Washakie County	56043	56043	Insuff Data	1/1/2015	7572.0	0.106	...	NaN	0.226	0.101	7940.0	0.099	47.0	50740	0.309	NaN	NaN
6107	WY	West	Mountain	Weston County	56045	56045	Insuff Data	1/1/2014	5633.0	0.162	...	NaN	0.201	0.084	6906.0	0.130	28.0	53665	0.232	NaN	0.171
6108	WY	West	Mountain	Weston County	56045	56045	Insuff Data	1/1/2015	7819.0	0.162	...	NaN	0.189	0.092	7238.0	0.130	28.0	59314	0.235	NaN	NaN

	County	Dentists	per capita
7	Garden County	53.0	0.027944
6	Deuel County	103.0	0.027414
1	Dolores County	49.0	0.013246

	tipo	valor
0	Grupos paramilitares	8903
1	Grupos armados no identificados	6406
2	Guerrillas	3899
3	Fuerza pública	2399